package com.fau.om.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.struts2.ServletActionContext;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.fau.om.entity.MedicalAdmin;
import com.fau.om.entity.MedicalGroup;
import com.fau.om.entity.MedicalMediaInfo;
import com.fau.om.entity.MedicalOrdersCheckSta;
import com.fau.om.entity.MedicalOrdersMediaSta;
import com.fau.om.entity.MedicalOrdersProSta;
import com.fau.om.entity.MedicalOrdersProvinceSta;
import com.fau.om.entity.MedicalOrdersRidepinSta;
import com.fau.om.service.IStatisticsManagerService;
import com.fau.om.util.DateUtil;
import com.fau.om.util.Page;
import com.fau.om.util.ParamUtil;
import com.fau.om.util.SystemConfig;

@Service
@Transactional(readOnly = true)
public class StatisticsManagerService extends BaseService implements
		IStatisticsManagerService {

	@SuppressWarnings("unchecked")
	@Override
	public Page findMediaStatisticsByPage(MedicalOrdersMediaSta mediaSta, MedicalAdmin admin, Map<String, Object> map) {
		
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersMediaSta a where 1=1 and a.hospitalId=? ");
		StringBuilder countSql = new StringBuilder("select count(distinct a.mediaId) from MedicalOrdersMediaSta a where 1=1 and a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(admin.getEnterpriseId());
		
		Date startTime = null;
		Date endTime = null;
		if(StringUtils.isNotBlank(mediaSta.getStartTime()) && StringUtils.isNotBlank(mediaSta.getEndTime())) {
			startTime = DateUtil.getDate(mediaSta.getStartTime());
			endTime = DateUtil.getDate(mediaSta.getEndTime());
			statusSql.append("and a.ordersTime between ? and ? ");
			countSql.append("and a.ordersTime between ? and ? ");
			objectList.add(startTime);
			objectList.add(endTime);
		}
		
		statusSql.append("group by a.mediaId ");
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map, "pageIndex", 1), ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
		
		List<MedicalOrdersMediaSta> mediaStaList = new ArrayList<MedicalOrdersMediaSta>();
		List<Object> objList1 = new ArrayList<Object>();
		List<Object> objList2 = new ArrayList<Object>();
		List<Object> objList3 = new ArrayList<Object>();
		List<Object> objList4 = new ArrayList<Object>();
		for(Object obj : page.getResult()) {
			MedicalOrdersMediaSta bean = (MedicalOrdersMediaSta) obj;
			
			// 订单数
			StringBuilder ordersCountHql = new StringBuilder("select count(*) from MedicalOrdersMediaSta a where 1=1 and a.mediaId=? ");
			// 货款金额
			StringBuilder commodityMoneyHql = new StringBuilder("select sum(a.commodityMoney) from MedicalOrdersMediaSta a where 1=1 and a.mediaId=? ");
			// 运费和
			StringBuilder freightHql = new StringBuilder("select sum(a.freight) from MedicalOrdersMediaSta a where 1=1 and a.mediaId=? ");
			// 搭销单
			StringBuilder ridePinHql = new StringBuilder("select count(*) from MedicalOrdersMediaSta a where 1=1 and a.mediaId=? and a.ridePin=? ");
			objList1.add(bean.getMediaId());
			objList2.add(bean.getMediaId());
			objList3.add(bean.getMediaId());
			objList4.add(bean.getMediaId());
			objList4.add(SystemConfig.RIDEPIN_ORDERS_TYPE);
			
			if(StringUtils.isNotBlank(mediaSta.getStartTime()) && StringUtils.isNotBlank(mediaSta.getEndTime())) {
				startTime = DateUtil.getDate(mediaSta.getStartTime());
				endTime = DateUtil.getDate(mediaSta.getEndTime());
				ordersCountHql.append("and a.ordersTime between ? and ? ");
				commodityMoneyHql.append("and a.ordersTime between ? and ? ");
				freightHql.append("and a.ordersTime between ? and ? ");
				ridePinHql.append("and a.ordersTime between ? and ? ");
				objList1.add(startTime);
				objList1.add(endTime);
				objList2.add(startTime);
				objList2.add(endTime);
				objList3.add(startTime);
				objList3.add(endTime);
				objList4.add(startTime);
				objList4.add(endTime);
			}
			
			int ordersCount = this.iBaseDao.getIntCount(ordersCountHql.toString(), objList1.toArray());
			List<BigDecimal> commodityMoneyList = this.iBaseDao.getBySql(commodityMoneyHql.toString(), null, objList2.toArray());
			List<BigDecimal> freightList = this.iBaseDao.getBySql(freightHql.toString(), null, objList3.toArray());
			int ridePin = this.iBaseDao.getIntCount(ridePinHql.toString(), objList4.toArray());
			bean.setOrdersCount(ordersCount);
			bean.setTcommodityMoney(commodityMoneyList.get(0));
			bean.setTfreight(freightList.get(0));
			bean.setTotalMoney(commodityMoneyList.get(0).add(freightList.get(0)));
			bean.setRidePinCount(ridePin);
			bean.setRidePinProportion(new BigDecimal(ridePin).divide(new BigDecimal(ordersCount), 2, BigDecimal.ROUND_HALF_EVEN));
			mediaStaList.add(bean);
			objList1.clear();
			objList2.clear();
			objList3.clear();
			objList4.clear();
		}
		page.setResult(mediaStaList);
		return page;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook mediaStaExport(MedicalOrdersMediaSta mediaSta) throws Exception {
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersMediaSta a where a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(user.getEnterpriseId());
		
		if(null != mediaSta.getStime() && null != mediaSta.getEtime()){
			statusSql.append("and a.ordersTime between ? and ? ");
			objectList.add(mediaSta.getStime());
			objectList.add(mediaSta.getEtime());
		}
		statusSql.append("group by a.mediaId ");
		List<MedicalOrdersMediaSta> mediaStaList = this.iBaseDao.getBySql(statusSql.toString(), null, objectList.toArray());
		int size = mediaStaList.size();
		
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.ORDERS_MEDIA_FILE);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
				
		List<Object> objList1 = new ArrayList<Object>();
		List<Object> objList2 = new ArrayList<Object>();
		List<Object> objList3 = new ArrayList<Object>();
		List<Object> objList4 = new ArrayList<Object>();
		
		for(int i = 0;i < size;i++) {
			Row currRow = sheet.createRow(i + 1);
			
			currRow.createCell(0).setCellValue(mediaStaList.get(i).getMediaName());
			
			// 订单数
			StringBuilder ordersCountHql = new StringBuilder("select count(*) from MedicalOrdersMediaSta a where 1=1 and a.mediaId=? ");
			// 货款金额
			StringBuilder commodityMoneyHql = new StringBuilder("select sum(a.commodityMoney) from MedicalOrdersMediaSta a where 1=1 and a.mediaId=? ");
			// 运费和
			StringBuilder freightHql = new StringBuilder("select sum(a.freight) from MedicalOrdersMediaSta a where 1=1 and a.mediaId=? ");
			// 搭销单
			StringBuilder ridePinHql = new StringBuilder("select count(*) from MedicalOrdersMediaSta a where 1=1 and a.mediaId=? and a.ridePin=? ");
			objList1.add(mediaStaList.get(i).getMediaId());
			objList2.add(mediaStaList.get(i).getMediaId());
			objList3.add(mediaStaList.get(i).getMediaId());
			objList4.add(mediaStaList.get(i).getMediaId());
			objList4.add(SystemConfig.RIDEPIN_ORDERS_TYPE);
			
			if(null != mediaSta.getStime() && null != mediaSta.getEtime()){
				ordersCountHql.append("and a.ordersTime between ? and ? ");
				commodityMoneyHql.append("and a.ordersTime between ? and ? ");
				freightHql.append("and a.ordersTime between ? and ? ");
				ridePinHql.append("and a.ordersTime between ? and ? ");
				objList1.add(mediaSta.getStime());
				objList1.add(mediaSta.getEtime());
				objList2.add(mediaSta.getStime());
				objList2.add(mediaSta.getEtime());
				objList3.add(mediaSta.getStime());
				objList3.add(mediaSta.getEtime());
				objList4.add(mediaSta.getStime());
				objList4.add(mediaSta.getEtime());
			}
			
			int ordersCount = this.iBaseDao.getIntCount(ordersCountHql.toString(), objList1.toArray());
			List<BigDecimal> commodityMoneyList = this.iBaseDao.getBySql(commodityMoneyHql.toString(), null, objList2.toArray());
			List<BigDecimal> freightList = this.iBaseDao.getBySql(freightHql.toString(), null, objList3.toArray());
			int ridePin = this.iBaseDao.getIntCount(ridePinHql.toString(), objList4.toArray());
			
			currRow.createCell(1).setCellValue(ordersCount);
			currRow.createCell(2).setCellValue(commodityMoneyList.get(0).toString());
			currRow.createCell(3).setCellValue(freightList.get(0).toString());
			currRow.createCell(4).setCellValue(commodityMoneyList.get(0).add(freightList.get(0)).toString());
			currRow.createCell(5).setCellValue(ridePin);
			BigDecimal decimal = new BigDecimal(ridePin).divide(new BigDecimal(ordersCount), 2, BigDecimal.ROUND_HALF_EVEN);
			currRow.createCell(6).setCellValue(decimal.toString());
			objList1.clear();
			objList2.clear();
			objList3.clear();
			objList4.clear();
		}
		return workbook;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalOrdersProSta> findProSalesSta(MedicalOrdersProSta proSta,
			MedicalAdmin admin) {
		
		// 订单数
		StringBuilder ordersCountHql = new StringBuilder("select count(*), a.productName from MedicalOrdersProSta a where 1=1 and a.hospitalId=? ");
		// 订货数
		StringBuilder orderingCountHql = new StringBuilder("select sum(a.orderingCount) from MedicalOrdersProSta a where 1=1 and a.hospitalId=? ");
		// 订货额
		StringBuilder orderingPriceHql = new StringBuilder("select sum(a.orderingPrice) from MedicalOrdersProSta a where 1=1 and a.hospitalId=? ");
		// 运费
		StringBuilder freightHql = new StringBuilder("select sum(a.freight) from MedicalOrdersProSta a where 1=1 and a.hospitalId=? ");
		
		List<Object> objList = new ArrayList<Object>();
		objList.add(admin.getEnterpriseId());
		
		if(StringUtils.isNotBlank(proSta.getStartTime()) && StringUtils.isNotBlank(proSta.getEndTime())) {
			Date startTime = DateUtil.getDate(proSta.getStartTime());
			Date endTime = DateUtil.getDate(proSta.getEndTime());
			ordersCountHql.append("and a.ordersTime between ? and ? ");
			orderingCountHql.append("and a.ordersTime between ? and ? ");
			orderingPriceHql.append("and a.ordersTime between ? and ? ");
			freightHql.append("and a.ordersTime between ? and ? ");
			objList.add(startTime);
			objList.add(endTime);
		}
		
		if(StringUtils.isNotBlank(proSta.getGroupId())) {
			ordersCountHql.append("and a.groupId=? ");
			orderingCountHql.append("and a.groupId=? ");
			orderingPriceHql.append("and a.groupId=? ");
			freightHql.append("and a.groupId=? ");
			objList.add(proSta.getGroupId());
		}
		
		if(StringUtils.isNotBlank(proSta.getMediaId())) {
			ordersCountHql.append("and a.mediaId=? ");
			orderingCountHql.append("and a.mediaId=? ");
			orderingPriceHql.append("and a.mediaId=? ");
			freightHql.append("and a.mediaId=? ");
			objList.add(proSta.getMediaId());
		}
		
		if(StringUtils.isNotBlank(proSta.getCusSerId())) {
			ordersCountHql.append("and a.mediaId=? ");
			orderingCountHql.append("and a.mediaId=? ");
			orderingPriceHql.append("and a.mediaId=? ");
			freightHql.append("and a.mediaId=? ");
			objList.add(proSta.getCusSerId());
		}
		
		if(StringUtils.isNotBlank(proSta.getProductName())) {
			ordersCountHql.append("and a.productName like ? ");
			orderingCountHql.append("and a.productName like ? ");
			orderingPriceHql.append("and a.productName like ? ");
			freightHql.append("and a.productName like ? ");
			objList.add("%"+ proSta.getProductName() + "%");
		}
		
		ordersCountHql.append("group by a.productId ");
		orderingCountHql.append("group by a.productId ");
		orderingPriceHql.append("group by a.productId ");
		freightHql.append("group by a.productId ");
		
		List<Long> orderingCountList = this.iBaseDao.getBySql(orderingCountHql.toString(), null, objList.toArray());
		
		List<BigDecimal> orderingPriceList = this.iBaseDao.getBySql(orderingPriceHql.toString(), null, objList.toArray());
		
		List<BigDecimal> freightList = this.iBaseDao.getBySql(freightHql.toString(), null, objList.toArray());
		
		List<Object> objList1 = this.iBaseDao.getBySql(ordersCountHql.toString(), null, objList.toArray());
		List<MedicalOrdersProSta> list = new ArrayList<MedicalOrdersProSta>();
		for(int i=0; i<objList1.size(); i++) {
			proSta = new MedicalOrdersProSta();
			Object[] data = (Object[]) objList1.get(i);
			proSta.setOrdersCount(data[0].toString());
			proSta.setProductName(data[1].toString());
			proSta.setOrderingCount(orderingCountList.get(i).intValue());
			proSta.setOrderingPrice(orderingPriceList.get(i));
			proSta.setFreight(freightList.get(i));
			proSta.setTotalMoney(orderingPriceList.get(i).add(freightList.get(i)));
			list.add(proSta);
		}
		return list;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook productStaExport(MedicalOrdersProSta proSta) throws Exception {
		
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		// 订单数
		StringBuilder ordersCountHql = new StringBuilder("select count(*), a.productName from MedicalOrdersProSta a where 1=1 and a.hospitalId=? ");
		// 订货数
		StringBuilder orderingCountHql = new StringBuilder("select sum(a.orderingCount) from MedicalOrdersProSta a where 1=1 and a.hospitalId=? ");
		// 订货额
		StringBuilder orderingPriceHql = new StringBuilder("select sum(a.orderingPrice) from MedicalOrdersProSta a where 1=1 and a.hospitalId=? ");
		// 运费
		StringBuilder freightHql = new StringBuilder("select sum(a.freight) from MedicalOrdersProSta a where 1=1 and a.hospitalId=? ");
		
		List<Object> objList = new ArrayList<Object>();
		objList.add(user.getEnterpriseId());
		
		if(null != proSta.getStime() && null != proSta.getEtime()){
			ordersCountHql.append("and a.ordersTime between ? and ? ");
			orderingCountHql.append("and a.ordersTime between ? and ? ");
			orderingPriceHql.append("and a.ordersTime between ? and ? ");
			freightHql.append("and a.ordersTime between ? and ? ");
			objList.add(proSta.getStime());
			objList.add(proSta.getEtime());
		}
		
		if(StringUtils.isNotBlank(proSta.getGroupId())) {
			ordersCountHql.append("and a.groupId=? ");
			orderingCountHql.append("and a.groupId=? ");
			orderingPriceHql.append("and a.groupId=? ");
			freightHql.append("and a.groupId=? ");
			objList.add(proSta.getGroupId());
		}
		
		if(StringUtils.isNotBlank(proSta.getMediaId())) {
			ordersCountHql.append("and a.mediaId=? ");
			orderingCountHql.append("and a.mediaId=? ");
			orderingPriceHql.append("and a.mediaId=? ");
			freightHql.append("and a.mediaId=? ");
			objList.add(proSta.getMediaId());
		}
		
		if(StringUtils.isNotBlank(proSta.getCusSerId())) {
			ordersCountHql.append("and a.mediaId=? ");
			orderingCountHql.append("and a.mediaId=? ");
			orderingPriceHql.append("and a.mediaId=? ");
			freightHql.append("and a.mediaId=? ");
			objList.add(proSta.getCusSerId());
		}
		
		if(StringUtils.isNotBlank(proSta.getProductName())) {
			ordersCountHql.append("and a.productName like ? ");
			orderingCountHql.append("and a.productName like ? ");
			orderingPriceHql.append("and a.productName like ? ");
			freightHql.append("and a.productName like ? ");
			objList.add("%"+ proSta.getProductName() + "%");
		}
		
		ordersCountHql.append("group by a.productId ");
		orderingCountHql.append("group by a.productId ");
		orderingPriceHql.append("group by a.productId ");
		freightHql.append("group by a.productId ");
		
		List<Long> orderingCountList = this.iBaseDao.getBySql(orderingCountHql.toString(), null, objList.toArray());
		
		List<BigDecimal> orderingPriceList = this.iBaseDao.getBySql(orderingPriceHql.toString(), null, objList.toArray());
		
		List<BigDecimal> freightList = this.iBaseDao.getBySql(freightHql.toString(), null, objList.toArray());
		
		List<Object> objList1 = this.iBaseDao.getBySql(ordersCountHql.toString(), null, objList.toArray());
		
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.ORDERS_PRODUCT_FILE);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int size = objList1.size();
		for(int i=0; i<size; i++) {
			Row currRow = sheet.createRow(i + 1);
			Object[] data = (Object[]) objList1.get(i);
			currRow.createCell(1).setCellValue(data[0].toString());
			currRow.createCell(0).setCellValue(data[1].toString());
			currRow.createCell(2).setCellValue(orderingCountList.get(i).intValue());
			currRow.createCell(3).setCellValue(orderingPriceList.get(i).toString());
			currRow.createCell(4).setCellValue(freightList.get(i).toString());
			currRow.createCell(5).setCellValue(orderingPriceList.get(i).add(freightList.get(i)).toString());
		}
		return workbook;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalGroup> findAllCurSerGroup(MedicalAdmin admin) {
		return this.iBaseDao.getBySql("from MedicalGroup a where 1=1 and a.platform.eid=? and a.platform.platformType=? ", null, admin.getEnterpriseId(), SystemConfig.CALL_GROUP);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalAdmin> findCurSerByGroupId(String groupId) {
		return this.iBaseDao.getBySql("from MedicalAdmin a where a.group.id=? ", null, groupId);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalMediaInfo> findMediaInfo(MedicalAdmin admin) {
		return this.iBaseDao.getBySql("from MedicalMediaInfo a where a.eid=? ", null, admin.getEnterpriseId());
	}

	@Override
	public Page findCheckUserOrdersSta(MedicalOrdersProSta proSta, MedicalAdmin admin, Map<String, Object> map) {
		
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersCheckSta a where 1=1 and a.hospitalId=? ");
		StringBuilder countSql = new StringBuilder("select count(distinct a.checkUserId) from MedicalOrdersCheckSta a where 1=1 and a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(admin.getEnterpriseId());
		
		Date startTime = null;
		Date endTime = null;
		if(StringUtils.isNotBlank(proSta.getStartTime()) && StringUtils.isNotBlank(proSta.getEndTime())) {
			startTime = DateUtil.getDate(proSta.getStartTime());
			endTime = DateUtil.getDate(proSta.getEndTime());
			statusSql.append("and a.checkTime between ? and ? ");
			countSql.append("and a.checkTime between ? and ? ");
			objectList.add(startTime);
			objectList.add(endTime);
		}
		
		statusSql.append("group by a.checkUserId ");
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map, "pageIndex", 1), ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
		
		List<MedicalOrdersCheckSta> CheckStaList = new ArrayList<MedicalOrdersCheckSta>();
		List<Object> objList1 = new ArrayList<Object>();
		List<Object> objList2 = new ArrayList<Object>();
		List<Object> objList3 = new ArrayList<Object>();
		List<Object> objList4 = new ArrayList<Object>();
		for(Object obj : page.getResult()) {
			MedicalOrdersCheckSta bean = (MedicalOrdersCheckSta) obj;
			
			StringBuilder checkPassHql = new StringBuilder("select count(*) from MedicalOrdersCheckSta a where 1=1 and a.checkUserId=? and a.checkType=? ");
			StringBuilder checkNoPassHql = new StringBuilder("select count(*) from MedicalOrdersCheckSta a where 1=1 and a.checkUserId=? and a.checkType=? ");
			StringBuilder checkInvalidHql = new StringBuilder("select count(*) from MedicalOrdersCheckSta a where 1=1 and a.checkUserId=? and a.checkType=? ");
			StringBuilder checkCancelHql = new StringBuilder("select count(*) from MedicalOrdersCheckSta a where 1=1 and a.checkUserId=? and a.checkType=? ");
			
			objList1.add(bean.getCheckUserId());
			objList2.add(bean.getCheckUserId());
			objList3.add(bean.getCheckUserId());
			objList4.add(bean.getCheckUserId());
			
			objList1.add(SystemConfig.ORDERS_AUDIT_PASS_TYPE);		// 核过单数
			objList2.add(SystemConfig.ORDERS_AUDIT_NOPASS_TYPE);	// 未核过单数	
			objList3.add(SystemConfig.ORDERS_AUDIT_CANCEL_TYPE);	// 取消单数
			objList4.add(SystemConfig.ORDERS_AUDIT_INVALID_TYPE);	// 无效单数
			if(StringUtils.isNotBlank(proSta.getStartTime()) && StringUtils.isNotBlank(proSta.getEndTime())) {
				startTime = DateUtil.getDate(proSta.getStartTime());
				endTime = DateUtil.getDate(proSta.getEndTime());
				checkPassHql.append("and a.checkTime between ? and ? ");
				checkNoPassHql.append("and a.checkTime between ? and ? ");
				checkCancelHql.append("and a.checkTime between ? and ? ");
				checkInvalidHql.append("and a.checkTime between ? and ? ");
				objList1.add(startTime);
				objList2.add(startTime);
				objList3.add(startTime);
				objList4.add(startTime);
				objList1.add(endTime);
				objList2.add(endTime);
				objList3.add(endTime);
				objList4.add(endTime);
			}
			
			int checkPass = this.iBaseDao.getIntCount(checkPassHql.toString(), objList1.toArray());
			int checkNoPass = this.iBaseDao.getIntCount(checkNoPassHql.toString(), objList2.toArray());
			int checkCancel = this.iBaseDao.getIntCount(checkCancelHql.toString(), objList3.toArray());
			int checkInvalid = this.iBaseDao.getIntCount(checkCancelHql.toString(), objList4.toArray());
			bean.setCheckPass(checkPass);
			bean.setCheckNoPass(checkNoPass);
			bean.setCheckCancel(checkCancel);
			bean.setCheckInvalid(checkInvalid);
			
			// 核单总数
			int totalCheckCount = checkPass + checkNoPass + checkCancel + checkInvalid;
			bean.setTotalCheckCount(totalCheckCount);
			
			BigDecimal NoPassProportion = new BigDecimal(checkNoPass).divide(new BigDecimal(totalCheckCount), 4, RoundingMode.HALF_UP);
			bean.setNoPassProportion(NoPassProportion);
			CheckStaList.add(bean);
			objList1.clear();
			objList2.clear();
			objList3.clear();
			objList4.clear();
		}
		page.setResult(CheckStaList);
		return page;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook export(MedicalOrdersProSta proSta) throws Exception {
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersCheckSta a where a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(user.getEnterpriseId());
		
		if(null != proSta.getStime() && null != proSta.getEtime()){
			statusSql.append("and a.checkTime between ? and ? ");
			objectList.add(proSta.getStime());
			objectList.add(proSta.getEtime());
		}
		
		statusSql.append("group by a.checkUserId ");
		List<MedicalOrdersCheckSta> checkStaList = this.iBaseDao.getBySql(statusSql.toString(), null, objectList.toArray());
		
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.ORDERS_CHECK_MAN_FILE);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		
		int size = checkStaList.size();
		for(int i = 0;i < size;i++) {
			List<Object> objList1 = new ArrayList<Object>();
			List<Object> objList2 = new ArrayList<Object>();
			List<Object> objList3 = new ArrayList<Object>();
			Row currRow = sheet.createRow(i + 1);
			
			currRow.createCell(0).setCellValue(checkStaList.get(i).getCheckUserName());
			
			StringBuilder checkPassHql = new StringBuilder("select count(*) from MedicalOrdersCheckSta a where 1=1 and a.checkUserId=? and a.checkType=? ");
			StringBuilder checkNoPassHql = new StringBuilder("select count(*) from MedicalOrdersCheckSta a where 1=1 and a.checkUserId=? and a.checkType=? ");
			StringBuilder checkCancelHql = new StringBuilder("select count(*) from MedicalOrdersCheckSta a where 1=1 and a.checkUserId=? and a.checkType=? ");
			
			objList1.add(checkStaList.get(i).getCheckUserId());
			objList2.add(checkStaList.get(i).getCheckUserId());
			objList3.add(checkStaList.get(i).getCheckUserId());
			
			objList1.add(SystemConfig.ORDERS_AUDIT_PASS_TYPE);		// 核过单数
			objList2.add(SystemConfig.ORDERS_AUDIT_NOPASS_TYPE);	// 未核过单数	
			objList3.add(SystemConfig.ORDERS_AUDIT_CANCEL_TYPE);	// 取消单数
			if(null != proSta.getStime() && null != proSta.getEtime()){
				checkPassHql.append("and a.checkTime between ? and ? ");
				checkNoPassHql.append("and a.checkTime between ? and ? ");
				checkCancelHql.append("and a.checkTime between ? and ? ");
				objList1.add(proSta.getStime());
				objList2.add(proSta.getStime());
				objList3.add(proSta.getStime());
				objList1.add(proSta.getEtime());
				objList2.add(proSta.getEtime());
				objList3.add(proSta.getEtime());
			}
			
			int checkPass = this.iBaseDao.getIntCount(checkPassHql.toString(), objList1.toArray());
			currRow.createCell(2).setCellValue(checkPass);
			int checkNoPass = this.iBaseDao.getIntCount(checkNoPassHql.toString(), objList2.toArray());
			currRow.createCell(3).setCellValue(checkNoPass);
			int checkCancel = this.iBaseDao.getIntCount(checkCancelHql.toString(), objList3.toArray());
			currRow.createCell(4).setCellValue(checkCancel);
			
			// 核单总数
			int totalCheckCount = checkPass + checkNoPass + checkCancel;
			currRow.createCell(1).setCellValue(totalCheckCount);
			
			BigDecimal NoPassProportion = new BigDecimal(checkNoPass).divide(new BigDecimal(totalCheckCount), 2, BigDecimal.ROUND_HALF_EVEN);
			currRow.createCell(5).setCellValue(NoPassProportion.toString());
			
		}
		return workbook;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Page findProvinceStatisticsByPage(MedicalOrdersProvinceSta provinceSta, MedicalAdmin admin, Map<String, Object> map) {
		
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersProvinceSta a where 1=1 and a.hospitalId=? ");
		StringBuilder countSql = new StringBuilder("select count(distinct a.provinceId) from MedicalOrdersProvinceSta a where 1=1 and a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(admin.getEnterpriseId());
		
		Date startTime = null;
		Date endTime = null;
		if(StringUtils.isNotBlank(provinceSta.getStartTime()) && StringUtils.isNotBlank(provinceSta.getEndTime())) {
			startTime = DateUtil.getDate(provinceSta.getStartTime());
			endTime = DateUtil.getDate(provinceSta.getEndTime());
			statusSql.append("and a.ordersTime between ? and ? ");
			countSql.append("and a.ordersTime between ? and ? ");
			objectList.add(startTime);
			objectList.add(endTime);
		}
		
		statusSql.append("group by a.provinceId ");
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map, "pageIndex", 1), ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
		
		List<MedicalOrdersProvinceSta> provinceStaList = new ArrayList<MedicalOrdersProvinceSta>();
		
		List<Object> objList0 = new ArrayList<Object>();
		List<Object> objList1 = new ArrayList<Object>();
		List<Object> objList2 = new ArrayList<Object>();
		List<Object> objList3 = new ArrayList<Object>();
		for(Object obj : page.getResult()) {
			MedicalOrdersProvinceSta bean = (MedicalOrdersProvinceSta) obj;
			// 总单数
			StringBuilder totalCountMoneyHql = new StringBuilder("select count(*), sum(a.commodityMoney) from MedicalOrdersProvinceSta a where 1=1 and a.hospitalId=? and a.provinceId=? ");
			objList0.add(bean.getHospitalId());
			objList0.add(bean.getProvinceId());
			// 妥投单数、妥投金额
			StringBuilder postCountMoneyHql = new StringBuilder("select count(*), sum(a.commodityMoney) from MedicalOrdersProvinceSta a where 1=1 and a.hospitalId=? and a.provinceId=? and a.deliveryType=? ");
			objList1.add(bean.getHospitalId());
			objList1.add(bean.getProvinceId());
			objList1.add(SystemConfig.ORDERS_SIGN_TYPE);
			// 退回单数、退回金额
			StringBuilder backCountMoneyHql = new StringBuilder("select count(*), sum(a.commodityMoney) from MedicalOrdersProvinceSta a where 1=1 and a.hospitalId=? and a.provinceId=? and a.deliveryType=? ");
			objList2.add(bean.getHospitalId());
			objList2.add(bean.getProvinceId());
			objList2.add(SystemConfig.ORDERS_RETREAT_TYPE);
			// 无反馈单数、无反馈金额
			StringBuilder feedbackHql = new StringBuilder("select count(*), sum(a.commodityMoney) from MedicalOrdersProvinceSta a where 1=1 and a.hospitalId=? and  a.provinceId=? and a.deliveryType=? ");
			objList3.add(bean.getHospitalId());
			objList3.add(bean.getProvinceId());
			objList3.add(SystemConfig.ORDERS_DELIVERY_TYPE);
			
			if(StringUtils.isNotBlank(provinceSta.getStartTime()) && StringUtils.isNotBlank(provinceSta.getEndTime())) {
				startTime = DateUtil.getDate(provinceSta.getStartTime());
				endTime = DateUtil.getDate(provinceSta.getEndTime());
				totalCountMoneyHql.append("and a.ordersTime between ? and ? ");
				postCountMoneyHql.append("and a.ordersTime between ? and ? ");
				backCountMoneyHql.append("and a.ordersTime between ? and ? ");
				feedbackHql.append("and a.ordersTime between ? and ? ");
				objList0.add(startTime);
				objList0.add(endTime);
				objList1.add(startTime);
				objList1.add(endTime);
				objList2.add(startTime);
				objList2.add(endTime);
				objList3.add(startTime);
				objList3.add(endTime);
			}
			
			BigDecimal tcommodityMoney = new BigDecimal(0);		// 总金额
			BigDecimal postMoney = new BigDecimal(0);			// 妥投金额
			BigDecimal backMoney = new BigDecimal(0);			// 退回金额
			BigDecimal feedbackMoney = new BigDecimal(0);		// 无反馈金额
			
			List<Object> totalCountMoneyList  = this.iBaseDao.getBySql(totalCountMoneyHql.toString(), null, objList0.toArray());
			Object[] obj0 = (Object[]) totalCountMoneyList.get(0);
			bean.setOrdersCount(Integer.parseInt(obj0[0].toString()));
			if(null != obj0[1]) {
				tcommodityMoney = new BigDecimal(obj0[1].toString());
			}
			
			List<Object> postCountMoneyList = this.iBaseDao.getBySql(postCountMoneyHql.toString(), null, objList1.toArray());
			Object[] obj1 = (Object[]) postCountMoneyList.get(0);
			bean.setPostCount(Integer.parseInt(obj1[0].toString()));
			if(null != obj1[1]) {
				postMoney = new BigDecimal(obj1[1].toString());
			}
			
			List<Object> backCountMoneyList = this.iBaseDao.getBySql(backCountMoneyHql.toString(), null, objList2.toArray());
			Object[] obj2 = (Object[]) backCountMoneyList.get(0);
			bean.setBackCount(Integer.parseInt(obj2[0].toString()));
			if(null != obj2[1]) {
				backMoney = new BigDecimal(obj2[1].toString());
			} 
			
			List<Object> feedbackCountMoneyList = this.iBaseDao.getBySql(feedbackHql.toString(), null, objList3.toArray());
			Object[] obj3 = (Object[]) feedbackCountMoneyList.get(0);
			bean.setFeedbackCount(Integer.parseInt(obj3[0].toString()));
			if(null != obj3[1]) {
				feedbackMoney = new BigDecimal(obj3[1].toString());
			} 
			
			bean.setTcommodityMoney(tcommodityMoney);
			bean.setPostMoney(postMoney);
			bean.setBackMoney(backMoney);
			bean.setFeedbackMoney(feedbackMoney);
			
			provinceStaList.add(bean);
			objList0.clear();
			objList1.clear();
			objList2.clear();
			objList3.clear();
		}
		page.setResult(provinceStaList);
		return page;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook provinceStaExport(MedicalOrdersProvinceSta provinceSta) throws Exception {
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersProvinceSta a where 1=1 and a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(user.getEnterpriseId());
		
		if(null != provinceSta.getStime() && null != provinceSta.getEtime()){
			statusSql.append("and a.ordersTime between ? and ? ");
			objectList.add(provinceSta.getStime());
			objectList.add(provinceSta.getEtime());
		}
		statusSql.append("group by a.provinceId ");
		
		List<MedicalOrdersProvinceSta> provinceStaList = this.iBaseDao.getBySql(statusSql.toString(), null, objectList.toArray());
		
		List<Object> objList0 = new ArrayList<Object>();
		List<Object> objList1 = new ArrayList<Object>();
		List<Object> objList2 = new ArrayList<Object>();
		List<Object> objList3 = new ArrayList<Object>();
		
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.ORDERS_PROVINCE_FILE);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		
		int size = provinceStaList.size();
		for(int i = 0;i < size;i++) {
			Row currRow = sheet.createRow(i + 1);
			
			currRow.createCell(0).setCellValue(provinceStaList.get(i).getProvinceName());
			
			// 总单数
			StringBuilder totalCountMoneyHql = new StringBuilder("select count(*), sum(a.commodityMoney) from MedicalOrdersProvinceSta a where 1=1 and a.hospitalId=? and a.provinceId=? ");
			objList0.add(provinceStaList.get(i).getHospitalId());
			objList0.add(provinceStaList.get(i).getProvinceId());
			// 妥投单数、妥投金额
			StringBuilder postCountMoneyHql = new StringBuilder("select count(*), sum(a.commodityMoney) from MedicalOrdersProvinceSta a where 1=1 and a.hospitalId=? and a.provinceId=? and a.deliveryType=? ");
			objList1.add(provinceStaList.get(i).getHospitalId());
			objList1.add(provinceStaList.get(i).getProvinceId());
			objList1.add(SystemConfig.ORDERS_SIGN_TYPE);
			// 退回单数、退回金额
			StringBuilder backCountMoneyHql = new StringBuilder("select count(*), sum(a.commodityMoney) from MedicalOrdersProvinceSta a where 1=1 and a.hospitalId=? and a.provinceId=? and a.deliveryType=? ");
			objList2.add(provinceStaList.get(i).getHospitalId());
			objList2.add(provinceStaList.get(i).getProvinceId());
			objList2.add(SystemConfig.ORDERS_RETREAT_TYPE);
			// 无反馈单数、无反馈金额
			StringBuilder feedbackHql = new StringBuilder("select count(*), sum(a.commodityMoney) from MedicalOrdersProvinceSta a where 1=1 and a.hospitalId=? and a.provinceId=? and a.deliveryType=? ");
			objList3.add(provinceStaList.get(i).getHospitalId());
			objList3.add(provinceStaList.get(i).getProvinceId());
			objList3.add(SystemConfig.ORDERS_DELIVERY_TYPE);
			
			if(null != provinceSta.getStime() && null != provinceSta.getEtime()){
				totalCountMoneyHql.append("and a.ordersTime between ? and ? ");
				postCountMoneyHql.append("and a.ordersTime between ? and ? ");
				backCountMoneyHql.append("and a.ordersTime between ? and ? ");
				feedbackHql.append("and a.ordersTime between ? and ? ");
				
				objList0.add(provinceSta.getStime());
				objList0.add(provinceSta.getEtime());
				objList1.add(provinceSta.getStime());
				objList1.add(provinceSta.getEtime());
				objList2.add(provinceSta.getStime());
				objList2.add(provinceSta.getEtime());
				objList3.add(provinceSta.getStime());
				objList3.add(provinceSta.getEtime());
			}
			
			BigDecimal tcommodityMoney = new BigDecimal(0);
			BigDecimal postMoney = new BigDecimal(0);
			BigDecimal backMoney = new BigDecimal(0);
			BigDecimal feedbackMoney = new BigDecimal(0);
			
			List<Object> totalCountMoneyList  = this.iBaseDao.getBySql(totalCountMoneyHql.toString(), null, objList0.toArray());
			Object[] obj0 = (Object[]) totalCountMoneyList.get(0);
			currRow.createCell(1).setCellValue(obj0[0].toString());
			if(null != obj0[1]) {
				tcommodityMoney = new BigDecimal(obj0[1].toString());
			}
			
			List<Object> postCountMoneyList = this.iBaseDao.getBySql(postCountMoneyHql.toString(), null, objList1.toArray());
			Object[] obj1 = (Object[]) postCountMoneyList.get(0);
			currRow.createCell(3).setCellValue(obj1[0].toString());
			if(null != obj1[1]) {
				postMoney = new BigDecimal(obj1[1].toString());
			}
			
			List<Object> backCountMoneyList = this.iBaseDao.getBySql(backCountMoneyHql.toString(), null, objList2.toArray());
			Object[] obj2 = (Object[]) backCountMoneyList.get(0);
			currRow.createCell(5).setCellValue(obj2[0].toString());
			if(null != obj2[1]) {
				backMoney = new BigDecimal(obj2[1].toString());
			} 
			
			List<Object> feedbackCountMoneyList = this.iBaseDao.getBySql(feedbackHql.toString(), null, objList3.toArray());
			Object[] obj3 = (Object[]) feedbackCountMoneyList.get(0);
			currRow.createCell(7).setCellValue(obj3[0].toString());
			if(null != obj3[1]) {
				feedbackMoney = new BigDecimal(obj3[1].toString());
			} 
			
			currRow.createCell(4).setCellValue(postMoney.toString());
			currRow.createCell(6).setCellValue(backMoney.toString());
			currRow.createCell(8).setCellValue(feedbackMoney.toString());
			currRow.createCell(2).setCellValue(tcommodityMoney.toString());
			
			objList0.clear();
			objList1.clear();
			objList2.clear();
			objList3.clear();
		}
		return workbook;
	}

	@Override
	public Page findridePinProSta(MedicalOrdersRidepinSta ridepinSta, MedicalAdmin admin, Map<String, Object> map) {
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersRidepinSta a where 1=1 and a.hospitalId=? ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalOrdersRidepinSta a where 1=1 and a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(admin.getEnterpriseId());
		
		Date startTime = null;
		Date endTime = null;
		if(StringUtils.isNotBlank(ridepinSta.getStartTime()) && StringUtils.isNotBlank(ridepinSta.getEndTime())) {
			startTime = DateUtil.getDate(ridepinSta.getStartTime());
			endTime = DateUtil.getDate(ridepinSta.getEndTime());
			statusSql.append("and a.ordersTime between ? and ? ");
			countSql.append("and a.ordersTime between ? and ? ");
			objectList.add(startTime);
			objectList.add(endTime);
		}
		
		if(StringUtils.isNotBlank(ridepinSta.getOrdersNum())) {
			statusSql.append("and a.ordersNum=? ");
			countSql.append("and a.ordersNum=? ");
			objectList.add(ridepinSta.getOrdersNum());
		}
		
		if(StringUtils.isNotBlank(ridepinSta.getMediaId())) {
			statusSql.append("and a.mediaId=? ");
			countSql.append("and a.mediaId=? ");
			objectList.add(ridepinSta.getMediaId());
		}
		
		if(StringUtils.isNotBlank(ridepinSta.getCusSerId())) {
			statusSql.append("and a.cusSerId=? ");
			countSql.append("and a.cusSerId=? ");
			objectList.add(ridepinSta.getCusSerId());
		}
		
		//排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils .isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])){
			statusSql.append("order by a." + sortField[0] + " " + sortOrder[0]); 
		} else {
			statusSql.append("order by a.ordersTime desc ");
		}
		return  this.iBaseDao.getPage(ParamUtil.getInteger(map, "pageIndex", 1), ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook ridePinStaExport(MedicalOrdersRidepinSta ridepinSta) throws Exception {
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersRidepinSta a where a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(user.getEnterpriseId());
		
		if(null != ridepinSta.getStime() && null != ridepinSta.getEtime()){
			statusSql.append("and a.ordersTime between ? and ? ");
			objectList.add(ridepinSta.getStime());
			objectList.add(ridepinSta.getEtime());
		}
		
		if(StringUtils.isNotBlank(ridepinSta.getOrdersNum())) {
			statusSql.append("and a.ordersNum=? ");
			objectList.add(ridepinSta.getOrdersNum());
		}
		
		if(StringUtils.isNotBlank(ridepinSta.getMediaId())) {
			statusSql.append("and a.mediaId=? ");
			objectList.add(ridepinSta.getMediaId());
		}
		
		if(StringUtils.isNotBlank(ridepinSta.getCusSerId())) {
			statusSql.append("and a.cusSerId=? ");
			objectList.add(ridepinSta.getCusSerId());
		}
		statusSql.append("order by a.ordersTime ");
		
		List<MedicalOrdersRidepinSta> list = this.iBaseDao.getBySql(statusSql.toString(), null, objectList.toArray());
		
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.ORDERS_ADDITION_FILE);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		
		int size = list.size();
		for (int i = 0; i < size; i++) {
			Row currRow = sheet.createRow(i + 1);
			
			currRow.createCell(0).setCellValue(list.get(i).getOrdersNum());
			currRow.createCell(1).setCellValue(DateFormatUtils.format(list.get(i).getOrdersTime(),"yyyy-MM-dd HH:mm:ss"));
			currRow.createCell(2).setCellValue(list.get(i).getProductDetail());
			currRow.createCell(3).setCellValue(list.get(i).getProductCount());
			currRow.createCell(4).setCellValue(list.get(i).getMainProduct());
			currRow.createCell(5).setCellValue(list.get(i).getRidePinProduct());
			currRow.createCell(6).setCellValue(list.get(i).getMediaName());
		}
		
		return workbook;
	}
	
}
